import traceback
import logging
import aiomysql
import asyncio
logobj = logging.getLogger('mysql')


class Pmysql:
    def __init__(self):
        self.coon = None
        self.pool = None

    async def initpool(self):
        try:
            logobj.debug("will connect mysql~")
            __pool = await aiomysql.create_pool(
                minsize=5,
                maxsize=10,
                host='112.74.189.60',
                port=3310,
                user='root',
                password='1234',
                db='flask_demo',
                autocommit=True)
            return __pool
        except:
            logobj.error('connect error.', exc_info=True)

    async def getCurosr(self):
        conn = await self.pool.acquire()
        cur = await conn.cursor()
        return conn, cur

    async def query(self, query, param=None):
        conn, cur = await self.getCurosr()
        try:
            await cur.execute(query, param)
            return await cur.fetchall()
        except:
            logobj.error(traceback.format_exc())
        finally:
            if cur:
                await cur.close()
            # 释放掉conn,将连接放回到连接池中
            await self.pool.release(conn)

    async def execute(self, query, param=None):
        conn, cur = await self.getCurosr()
        try:
            await cur.execute(query, param)
            if cur.rowcount == 0:
                return False
            else:
                return True
        except:
            logobj.error(traceback.format_exc())
        finally:
            if cur:
                await cur.close()
            # 释放掉conn,将连接放回到连接池中
            await self.pool.release(conn)


async def test(sem,index):
    async with sem:
        mysqlobj = await getAmysqlobj()
        # UPDATE `youku`.`person` SET `psName` = '张三丰' WHERE (`id` = '3');
        exeRtn = await mysqlobj.execute(f"INSERT INTO `flask_demo`.`user`(`id`, `username`) VALUES ({index}, 'zeng');")
        if exeRtn:
            print('操作成功')
        else:
            print('操作失败')


async def getAmysqlobj():
    mysqlobj = Pmysql()
    pool = await mysqlobj.initpool()
    mysqlobj.pool = pool
    return mysqlobj


if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    sem=asyncio.Semaphore(30)
    tasks_one = [asyncio.ensure_future(test(sem,i)) for i in range(100,2000)]
    loop.run_until_complete(asyncio.wait(tasks_one))
